﻿Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.Common
Imports System.Configuration

Public Class DLBase


#Region "Declaration"

    Public Const mscParamToken As String = "@"
    Const mscProcName_CheckDupplicateAlias As String = "Proc_CheckForDuplicatedAlias"
    Const mscProcName_Proc_SYS_SetAutoID As String = "Proc_SYS_SetAutoID"
    Const mscProcName_Proc_SYS_GetAutoID As String = "Proc_SYS_GetAutoID"

    Private Const mscConnectionStringPassword As String = "Password"

    Protected m_sDatabaseConnectionName As String = "TAF_Management_2010"

    Protected m_strTableName As String
    Protected m_strPrimaryKeyName As String

    Protected m_strStoreSelectAll As String
    Protected m_strStoreSelectDetailByRefID As String
    Protected m_strStoreDelete As String
    Protected m_strStoreInsert As String
    Protected m_strStoreUpdate As String

    Protected Shared db As Database

    Protected strSubSystemCode As String = String.Empty

#End Region

#Region "Property"

    ''' <summary>
    ''' Tên của chuỗi kết nối trong file configuration. Dùng để khởi tạo kết nối vào Database 
    ''' </summary>
    Public Property DatabaseConfigName() As String
        Get
            Return m_sDatabaseConnectionName
        End Get
        Set(ByVal value As String)
            m_sDatabaseConnectionName = value
            InitDatabase(m_sDatabaseConnectionName)
        End Set
    End Property

    Public Property PrimaryKey() As String
        Get
            Return m_strPrimaryKeyName
        End Get
        Set(ByVal value As String)
            m_strPrimaryKeyName = value
        End Set
    End Property

    Public Overridable Property TableName() As String
        Get
            Return m_strTableName
        End Get
        Set(ByVal value As String)
            m_strTableName = value
            m_strStoreSelectDetailByRefID = "Proc_" + strSubSystemCode + "_Select_" & m_strTableName & "Detail_ByRefID"
            m_strStoreSelectAll = "Proc_" + strSubSystemCode + "_SelectAll_" & m_strTableName & "s"
            m_strStoreDelete = "Proc_" + strSubSystemCode + "_Delete_" & m_strTableName
            m_strStoreInsert = "Proc_" + strSubSystemCode + "_Insert_" & m_strTableName
            m_strStoreUpdate = "Proc_" + strSubSystemCode + "_Update_" & m_strTableName
        End Set
    End Property

    Public Shared ReadOnly Property Connection() As DbConnection
        Get
            Return db.CreateConnection
        End Get
    End Property

    Public Sub InitDatabase(ByVal databaseConnectionName As String)
        db = GetDatabase(databaseConnectionName)
    End Sub

    ''' <summary>
    ''' Get Database from encrypted password in app.config
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetDatabase(ByVal databaseConnectionName As String) As Database
        Dim dbResult As Database = Nothing
        dbResult = DatabaseFactory.CreateDatabase(m_sDatabaseConnectionName)
        Return dbResult
    End Function

    ''' <summary>
    ''' Close tat ca cac Connection khi Logout
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Sub CloseDatabase()
        Database.ClearParameterCache()

        If db.GetType Is GetType(Sql.SqlDatabase) Then 'SQLServer
            SqlClient.SqlConnection.ClearAllPools()
        End If
        db = Nothing
    End Sub

#End Region

#Region "Constructor"

    Public Sub New(ByVal databaseConnectionName As String, ByVal strTableName As String)
        Me.TableName = strTableName
        Me.m_sDatabaseConnectionName = databaseConnectionName
        InitializeComponent(m_sDatabaseConnectionName)
    End Sub

    Public Sub New(ByVal databaseConnectionName As String)
        Me.m_sDatabaseConnectionName = databaseConnectionName
        InitializeComponent(m_sDatabaseConnectionName)
    End Sub

    Public Sub New(ByVal databaseConnectionName As String, ByVal strTableName As String, ByVal subSystem As String)
        Me.strSubSystemCode = subSystem
        Me.TableName = strTableName
        Me.m_sDatabaseConnectionName = databaseConnectionName
        InitializeComponent(m_sDatabaseConnectionName)
    End Sub

    Protected Overridable Sub InitializeComponent(ByVal databaseConnectionName As String)
        db = GetDatabase(databaseConnectionName)
    End Sub

#End Region

#Region "Method"

#Region "Get Method"

    Public Overridable Function GetAllData() As DataTable
        Return db.ExecuteDataSet(m_strStoreSelectAll).Tables(0)
    End Function

    Public Overridable Function GetAllData(ByVal ts As DbTransaction) As DataTable
        Dim ds As New DataSet
        db.LoadDataSet(ts, m_strStoreSelectAll, ds, New String() {m_strTableName})
        Return ds.Tables(0)
    End Function

    ''' <summary>
    ''' GetAllData
    ''' </summary>
    ''' <param name="table"></param>
    Public Overridable Sub GetAllData(ByRef table As DataTable)
        db.LoadDataSet(m_strStoreSelectAll, table.DataSet, New String() {table.TableName})
    End Sub

    Public Overridable Function GetAllDataFollowedOption(Optional ByVal optionParam() As Object = Nothing) As DataTable
        Dim ds As New DataSet
        If optionParam IsNot Nothing Then
            db.LoadDataSet(m_strStoreSelectAll & "_WithOption", ds, New String() {m_strTableName}, optionParam)
        Else
            db.LoadDataSet(m_strStoreSelectAll & "_NoParam", ds, New String() {m_strTableName})
        End If
        Return ds.Tables(0)
    End Function

    Public Overridable Sub GetAllDataFollowedOption(ByVal table As DataTable, Optional ByVal optionParam() As Object = Nothing)
        If optionParam IsNot Nothing Then
            db.LoadDataSet(m_strStoreSelectAll & "_WithOption", table.DataSet, New String() {table.TableName}, optionParam)
        Else
            db.LoadDataSet(m_strStoreSelectAll & "_NoParam", table.DataSet, New String() {table.TableName})
        End If
    End Sub
#End Region

#Region "Private Method"

#End Region

#Region "Protected Method"

    Protected Function UpdateTable(ByVal table As DataTable) As Boolean
        Dim mscSQL As String = "SELECT * FROM {0}"
        Dim intResult As Integer
        Dim da As DbDataAdapter
        Dim cb As DbCommandBuilder
        Dim cmdSelect As DbCommand

        Using cnnTemp As Common.DbConnection = DLBase.Connection
            With db
                cmdSelect = .GetSqlStringCommand(String.Format(mscSQL, table.TableName))
                da = .GetDataAdapter()
                da.SelectCommand = cmdSelect
                cb = .DbProviderFactory.CreateCommandBuilder()
            End With

            cb.ConflictOption = ConflictOption.OverwriteChanges

            cb.DataAdapter = da

            cmdSelect.Connection = cnnTemp
            intResult = da.Update(table)
        End Using

        Return (intResult > 0)
    End Function

    Protected Function UpdateTable(ByVal table As DataTable, ByVal ts As DbTransaction) As Boolean
        Dim mscSQL As String = "SELECT * FROM {0}"
        Dim intResult As Integer
        Dim da As DbDataAdapter
        Dim cb As DbCommandBuilder
        Dim cmdSelect As DbCommand

        With db
            cmdSelect = .GetSqlStringCommand(String.Format(mscSQL, table.TableName))
            da = .GetDataAdapter()
            da.SelectCommand = cmdSelect
            cb = .DbProviderFactory.CreateCommandBuilder()
        End With

        cb.ConflictOption = ConflictOption.OverwriteChanges

        cb.DataAdapter = da

        cmdSelect.Connection = ts.Connection
        cmdSelect.Transaction = ts

        intResult = da.Update(table)

        Return (intResult > 0)
    End Function
#End Region

#Region "Public Method"

#End Region

#Region "Overridable"

#End Region

#End Region

#Region "CRUD"

    Public Overridable Function Insert(ByRef row As DataRow) As Boolean
        Dim intResult As Integer
        intResult = CInt(ExecuteNoneQueryTypedParam(m_strStoreInsert, row))
        Return (intResult > 0)
    End Function

    Public Overridable Function Insert(ByRef row As DataRow, ByVal ts As DbTransaction) As Boolean
        Dim intResult As Integer
        intResult = CInt(ExecuteNoneQueryTypedParam(m_strStoreInsert, row, ts))
        Return (intResult > 0)
    End Function

    Public Overridable Function Delete(ByRef guiID As Guid) As Boolean
        Dim intResult As Integer
        intResult = CInt(db.ExecuteNonQuery(m_strStoreDelete, guiID))
        Return (intResult > 0)
    End Function

    Public Overridable Function Delete(ByRef strID As String) As Boolean
        Dim intResult As Integer
        intResult = CInt(db.ExecuteNonQuery(m_strStoreDelete, strID))
        Return (intResult > 0)
    End Function

    Public Overridable Function Update(ByRef row As DataRow) As Boolean
        Dim intResult As Integer
        intResult = CInt(ExecuteNoneQueryTypedParam(m_strStoreUpdate, row))
        Return (intResult > 0)
    End Function

    Public Overridable Function Update(ByRef row As DataRow, ByVal ts As DbTransaction) As Boolean
        Dim intResult As Integer
        intResult = CInt(ExecuteNoneQueryTypedParam(m_strStoreUpdate, row, ts))
        Return (intResult > 0)
    End Function

    Public Overridable Function UpdateData(ByVal table As DataTable, ByVal ts As DbTransaction) As Boolean
        Return UpdateTable(table, ts)
    End Function

    Public Overridable Function UpdateData(ByVal table As DataTable) As Boolean
        Return UpdateTable(table)
    End Function

    Public Overridable Function CheckForDuplicatedAlias(ByVal ParamArray arrOption As Object()) As Boolean
        Dim iResult As Integer
        iResult = db.ExecuteScalar(mscProcName_CheckDupplicateAlias, arrOption)

        Return iResult > 0
    End Function
#End Region

#Region "Common"

    Protected Function ExecuteNoneQueryTypedParam(ByVal storeName As String, ByVal row As DataRow) As Integer
        Dim cmd As DbCommand = db.GetStoredProcCommand(storeName)
        db.DiscoverParameters(cmd)
        For Each pr As DbParameter In cmd.Parameters
            If pr.Direction <> ParameterDirection.ReturnValue Then
                pr.Value = row(pr.ParameterName.Replace(mscParamToken, ""))
            End If
        Next
        Return db.ExecuteNonQuery(cmd)
    End Function

    Protected Function ExecuteNoneQueryTypedParam(ByVal storeName As String, ByVal row As DataRow, ByVal ts As DbTransaction) As Integer
        Dim cmd As DbCommand = db.GetStoredProcCommand(storeName)
        db.DiscoverParameters(cmd)
        For Each pr As DbParameter In cmd.Parameters
            If pr.Direction <> ParameterDirection.ReturnValue Then
                pr.Value = row(pr.ParameterName.Replace(mscParamToken, ""))
            End If
        Next
        Return db.ExecuteNonQuery(cmd, ts)
    End Function

    Protected Function ExecuteScalarTypedParam(ByVal storeName As String, ByVal row As DataRow) As Object
        Dim cmd As DbCommand = db.GetStoredProcCommand(storeName)
        db.DiscoverParameters(cmd)
        For Each pr As DbParameter In cmd.Parameters
            If pr.Direction <> ParameterDirection.ReturnValue Then
                pr.Value = row(pr.ParameterName.Replace(mscParamToken, ""))
            End If
        Next
        Return db.ExecuteScalar(cmd)
    End Function

    Protected Function ExecuteScalarTypedParam(ByVal storeName As String, ByVal row As DataRow, ByVal ts As DbTransaction) As Object
        Dim cmd As DbCommand = db.GetStoredProcCommand(storeName)
        db.DiscoverParameters(cmd)
        For Each pr As DbParameter In cmd.Parameters
            If pr.Direction <> ParameterDirection.ReturnValue Then
                pr.Value = row(pr.ParameterName.Replace(mscParamToken, ""))
            End If
        Next
        Return db.ExecuteScalar(cmd, ts)
    End Function


#End Region


#Region "Auto RefNo"

    ''' <summary>
    ''' Sinh moi RefNo bang cach truyen vao RefType
    ''' </summary>
    Public Function GetNewRefNo(ByVal RefType As Integer) As String
        Dim sNewRefNo As String = ""
        Dim ds As New DataSet
        db.LoadDataSet(mscProcName_Proc_SYS_GetAutoID, ds, New String() {"AutoID"}, RefType)
        If ds.Tables.Count > 0 AndAlso ds.Tables(0).Rows.Count > 0 Then
            Dim dr As DataRow = ds.Tables(0).Rows(0)
            If dr.Table.Columns.Contains("Prefix") AndAlso dr.Table.Columns.Contains("Value") Then
                Dim iValue As Integer = If(dr("Value") Is DBNull.Value, 0, CInt(dr("Value")))
                Dim iLength As Integer = If(dr("Length") Is DBNull.Value, 3, CInt(dr("Length")))
                If iValue.ToString.Length >= iLength Then
                    sNewRefNo = dr("Prefix").ToString & (iValue + 1).ToString & dr("Suffix").ToString
                Else
                    sNewRefNo = dr("Prefix").ToString & (iValue + 1).ToString.PadLeft(iLength, "0") & dr("Suffix").ToString
                End If

            End If
        End If
        Return sNewRefNo
    End Function

    ''' <summary>
    ''' Save vao bang AutoID dua vao RefType
    ''' </summary>
    Public Function UpdateAutoID(ByVal refType As Integer, ByVal strRefNo As String, Optional ByVal ts As DbTransaction = Nothing) As Boolean

        Dim sPreFix As String = ""
        Dim iValue As Integer = 0
        Dim sSuffix As String = ""
        Dim iLength As Integer = 0
        Dim iStartValueIndex As Integer = -1
        Dim iEndValueIndex As Integer = -1

        If strRefNo.Length > 0 Then
            Dim i As Integer = strRefNo.Length - 1
            While i >= 0
                If Char.IsNumber(strRefNo(i)) Then
                    If iEndValueIndex < 0 Then
                        iEndValueIndex = i
                    End If
                Else
                    If iEndValueIndex >= 0 AndAlso iStartValueIndex < 0 Then
                        iStartValueIndex = i + 1
                        Exit While
                    End If
                End If
                i -= 1
            End While

            If iEndValueIndex >= 0 AndAlso iStartValueIndex < 0 Then
                iStartValueIndex = 0
            End If
            If iStartValueIndex >= 0 Then
                sPreFix = strRefNo.Substring(0, iStartValueIndex)
                sSuffix = If(iEndValueIndex >= strRefNo.Length - 1, "", strRefNo.Substring(iEndValueIndex + 1))
                iLength = iEndValueIndex - iStartValueIndex + 1
                iValue = CInt(strRefNo.Substring(iStartValueIndex, iLength))
            Else
                Return True
            End If
        Else
            Return True
        End If


        If ts Is Nothing Then
            Return db.ExecuteNonQuery(mscProcName_Proc_SYS_SetAutoID, refType, sPreFix, iValue, sSuffix, iLength) > 0 'ExecuteNoneQueryTypedParam("Proc_DI_Update_AutoID", row)
        Else
            Return db.ExecuteNonQuery(ts, mscProcName_Proc_SYS_SetAutoID, refType, sPreFix, iValue, sSuffix, iLength) > 0
        End If
    End Function

#End Region

End Class
